using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

//数据库公用程序
namespace XunChi.DBHelper
{
    /// <summary>
    /// SqlServerHelper类旨在封装高性能，SqlClient常见用途的可扩展最佳实践。此类为抽象类，不允许实例化，在应用时直接调用即可。
    /// </summary>
    public abstract class SqlServerHelper
    {
        //连接字符串可以使用的英文名：User Id可以使用uid替换，Password可以使用pwd替换，必须全部变量要么全部英文，要么全部缩写，这样才会起效果。为了方便记忆，建议使用缩写英文字母，方便排错。
        /*连接字符串样例：
            <connectionStrings>
                <!--SqlServerHelper连接字符串设定-->
                <add name="SqlServerHelper" connectionString="server=.;uid=sa;pwd=123456;database=yourdatabase" />
                <!--Oracle连接字符串设定-->
                <add name="OracleHelper" connectionString="Data Source=orcl;User Id=system;Password=123456;Integrated Security=no" />
                <!--MySql连接字符串设定-->
                <add name="MySqlHelper" connectionString="server=localhost;uid=root;pwd=123456;database=mysqldatabase" />
            </connectionStrings>
         */

        //获取数据库连接字符串，其属于静态变量且只读，项目中所有文档可以直接使用，但不能修改。数据库连接信息：获取当前应用程序默认配置的 System.Configuration.AppSettingsSection 数据。
        public static readonly string SQLString = ConfigurationManager.AppSettings["SqlServerString"];

        //用于存储缓存参数的哈希表
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 判断是否为空的字符串并抛出异常信息。
        /// </summary>
        /// <param name="conString"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParameters"></param>
        /// <exception cref="ArgumentException"></exception>
        /// <exception cref="ArgumentNullException"></exception>

        public static void IsNullString(string conString,string cmdText)
        {
            if (string.IsNullOrEmpty(conString))
            {
                throw new ArgumentException($"“{nameof(conString)}”不能为 null 或空。", nameof(conString));
            }

            if (string.IsNullOrEmpty(cmdText))
            {
                throw new ArgumentException($"“{nameof(cmdText)}”不能为 null 或空。", nameof(cmdText));
            }
        }

        #region 执行返回数据集的查询命令

        /// <summary>
        /// 使用所提供的参数执行SqlCommand，返回连接字符串中指定的数据库的结果集。
        /// </summary>
        /// <remarks>
        /// 例如：SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conString">SqlConnection的有效连接字符串</param>
        /// <param name="cmdType">CommandType(存储过程、文本等)</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="cmdTimeout">设置等待命令执行所需的时间（以秒为单位）。默认值为 30 秒。</param>
        /// <param name="cmdParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>包含结果的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string conString, CommandType cmdType, string cmdText, int? cmdTimeout, params SqlParameter[] cmdParameters)
        {
            IsNullString(conString,cmdText);

             SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand();
            //获取或设置在终止尝试执行命令并生成错误之前的等待时间。必须设置这个，因为连接数据库数据量大的时候有时间限制。

            if (cmdTimeout!=null)
            {
                cmd.CommandTimeout = cmdTimeout.Value;
            }

            //我们在这里使用try/catch，因为如果方法抛出异常，我们希望关闭连接抛出代码，因为不存在数据读取器，因此指挥行为CloseConnection不起作用。
            try
            {
                PrepareCommand(cmd, con, null, cmdType, cmdText, cmdParameters);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 通过参数化查询返回DataSet的数据集。
        /// </summary>
        /// <remarks>
        /// 例如：SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conString">SqlConnection的有效连接字符串</param>
        /// <param name="commandType">CommandType(存储过程、文本等)</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="cmdParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>包含结果的SqlDataReader</returns>
        public static DataSet ExecuteDataSet(string conString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParameters)
        {
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand();

            //我们在这里使用try/catch，因为如果方法抛出异常，我们希望关闭连接抛出代码，因为不存在数据读取器，因此指挥行为。CloseConnection不起作用
            try
            {
                PrepareCommand(cmd, con, null, cmdType, cmdText, cmdParameters);
                DataSet ds = new DataSet();
                SqlDataAdapter da=new SqlDataAdapter(cmdText, conString);
                da.Fill(ds);
             
                return ds;
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 通过参数化查询返回DataTable的数据集。
        /// </summary>
        /// <remarks>
        /// 例如：SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conString">SqlConnection的有效连接字符串</param>
        /// <param name="commandType">CommandType(存储过程、文本等)</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="cmdParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>包含结果的SqlDataReader</returns>
        public static DataTable ExecuteDataTable(string conString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParameters)
        {
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand();

            //我们在这里使用try/catch，因为如果方法抛出异常，我们希望关闭连接抛出代码，因为不存在数据读取器，因此指挥行为。CloseConnection不起作用
            try
            {
                PrepareCommand(cmd, con, null, cmdType, cmdText, cmdParameters);
                DataTable dt= new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmdText, conString);
                da.Fill(dt);

                return dt;
            }
            catch(Exception ex)
            {
                con.Close();
                throw ex;
            }
        }

        #endregion

        /// <summary>
        /// 准备执行命令
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="conn">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction对象</param>
        /// <param name="cmdType">Cmd类型，例如：存储过程或文本</param>
        /// <param name="cmdText">命令文本，例如：Select * from Products</param>
        /// <param name="cmdParms">要在命令中使用的SqlParameters</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText,params SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) //这里似乎可以改进，因为如果连接已经打开就不必要重新打开数据库连接了。
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            if (cmdParms!=null&& cmdParms.Length > 0) //必须加后面这个判断因为数组cmdParms永远不可能为null。
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 根据参数对更新、插入连接字符串中指定的数据库执行命令返回受影响的行数。
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conString">SqlConnection的有效连接字符串</param>
        /// <param name="cmdType">CommandType（存储过程、文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="cmdParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>一个int，表示受命令影响的行数</returns>
        public static int ExecuteNonQuery(string conString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParameters)
        {
            using (SqlConnection conn = new SqlConnection(conString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);
                int affectedRows = cmd.ExecuteNonQuery();//对连接执行 Transact-SQL 语句并返回受影响的行数。
                cmd.Parameters.Clear();//清空Transact-SQL 语句或存储过程的参数。
                return affectedRows;
            }
        }

        /// <summary>
        /// 根据参数返回批量更新的记录数。
        /// </summary>
        /// <returns></returns>
        public int BatchUpdate()
        {
            int batchCount = 0;
            return batchCount;
        }

        /// <summary>
        /// 使用所提供的参数。对现有数据库连接执行SqlCommand（不返回结果集）
        /// </summary>
        /// <remarks>
        /// 例如：
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="con">现有数据库连接</param>
        /// <param name="cmdType">CommandType（存储过程、文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="cmdParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>一个int，表示受命令影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection con, CommandType cmdType, string cmdText, params SqlParameter[] cmdParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, con, null, cmdType, cmdText, cmdParameters);
            int affectedRows = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return affectedRows;
        }

        /// <summary>
        /// 使用所提供的参数使用现有的SQL事务执行SqlCommand（不返回结果集) 
        /// </summary>
        /// <remarks>
        /// 例如： 
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">一个现有的sql事务</param>
        /// <param name="cmdType">CommandType（存储过程、文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="cmdParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>一个int，表示受命令影响的行数：</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParameters);
            int affectedRows = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return affectedRows;
        }

        /// <summary>
        /// 使用所提供的参数，执行SqlCommand，返回连接字符串中指定的数据库中第一条记录的第一列的值。
        /// </summary>
        /// <remarks>
        /// 例如：Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conString">SqlConnection的有效连接字符串</param>
        /// <param name="cmdType">CommandType（存储过程、文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="cmdParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>应使用Convert将其转换为预期类型的对象。致｛类型}</returns>
        public static object ExecuteScalar(string conString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(conString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParameters);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj;
            }
        }

        /// <summary>
        /// 使用所提供的参数:对现有数据库连接执行SqlCommand，返回第一条记录的第一列
        /// </summary>
        /// <remarks>
        /// 例如：Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="con">现有数据库连接</param>
        /// <param name="commandType">CommandType（存储过程、文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="cmdParameters">用于执行命令的SqlParamers数组</param>
        /// <returns>应使用Convert.To(Type)将其转换为预期类型的对象。</returns>
        public static object ExecuteScalar(SqlConnection con, CommandType cmdType, string cmdText, params SqlParameter[] cmdParameters)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, con, null, cmdType, cmdText, cmdParameters);
            object obj = cmd.ExecuteScalar();//为结果集中的第一行的第一列，或者，如果结果集为空，则为 null 引用。
            cmd.Parameters.Clear();
            return obj;
        }

        #region 参数缓存到字典中去

        /// <summary>
        /// 将参数数组添加到缓存中
        /// </summary>
        /// <param name="cacheKey">参数缓存密钥</param>
        /// <param name="cmdParms">要缓存的SqlParamers数组</param>
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// 检索缓存参数
        /// </summary>
        /// <param name="cacheKey">用于查找参数的键</param>
        /// <returns>缓存的SqlParamers数组</returns>
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
            {
                return null;
            }

            //克隆参数变量名。
            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
            {
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
            }

            return clonedParms;
        }

        #endregion
    }
}